package orest.front.market;

import java.sql.Connection;

import net.ssta.common.LoginUser;
import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class MarketSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static MarketSql m_builder = new MarketSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static MarketSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    /**
     * 获取指定用户的技术需求信息
     * 
     * @param RequestKit
     * @return
     */
    public static String getTechneedByUseridSql()
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM T_TECHNEED T WHERE USERID = '").append(LoginUser.getUserId()).append("' ORDER BY SUBMITDATE DESC");
        //sb.append("SELECT * FROM T_TECHNEED T WHERE USERID = '1' ORDER BY SUBMITDATE DESC");
        System.out.println("---------------- "+sb.toString());
        return sb.toString();
    }
    public static String getTechneedListSql(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        //sb.append("SELECT * FROM T_TECHNEED T WHERE 1=1 AND IFCHECK='1' AND USERID= ");
        sb.append("SELECT * FROM T_TECHNEED T WHERE 1=1 ");
        
        //关键词，与标题、关键字匹配
        String keyword = kit.getParameter("keyword","");
        if(!keyword.equals("")){
        	sb.append(" AND (T.TITLE LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        }
        
        //所属行业分类
        String newenergy = kit.getParameter("NEWENERGYCLASS","");
        if(newenergy!=null&&!newenergy.equals("")){
        	sb.append(" AND T.NEWENERGYCLASS = '").append(newenergy).append("' ");
        }
        
        //合作方式
        String cooperateway = kit.getParameter("COOPERATEWAY","");
        if(cooperateway!=null&&!cooperateway.equals("")){
        	sb.append(" AND T.COOPERATEWAY = '").append(cooperateway).append("' ");
        }

        
        
        sb.append(" ORDER BY SUBMITDATE DESC");
        System.out.println(sb.toString());
        return sb.toString();
    }
    
    
    /**
     * 删除技术需求的sql
     * 
     * @param uuid:技术需求的唯一标识
     * 
     * @return：更改推荐状态sql字符串
     */
    public static String deleteTechneedSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_TECHNEED WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 用户自荐项目列表sql语句
     * 
     * @param RequestKit
     * @return
     */
    public static String getSelloneselfByUseridSql(String userid)
    {
        StringBuffer sb = new StringBuffer();
        //sb.append("SELECT * FROM T_SELLONESELF T WHERE 1=1 AND USERID ='1' ");
        sb.append("SELECT * FROM T_SELLONESELF T WHERE 1=1 AND USERID ='").append(LoginUser.getUserId()).append("' ");
        sb.append(" ORDER BY SUBMITDATE DESC");
        //System.out.println(sb.toString());
        return sb.toString();
    }
    
    
    /**
     * 删除自荐项目信息的sql
     * 
     * @param uuid:自荐项目的唯一标识
     * 
     * @return：删除自荐项目sql字符串
     */
    public static String deleteSelloneselfSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_SELLONESELF WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 用户投融资项目列表sql语句
     * 
     * @param RequestKit
     * @return
     */
    public static String getInvestByUseridSql(String userid)
    {
        StringBuffer sb = new StringBuffer();
        //sb.append("SELECT * FROM T_INVESTANDFINANCE T WHERE 1=1 AND USERID = '1' ");
        sb.append("SELECT * FROM T_INVESTANDFINANCE T WHERE 1=1 AND USERID ='").append(LoginUser.getUserId()).append("'");
        sb.append(" ORDER BY SUBMITDATE DESC");
        //System.out.println(sb.toString());
        return sb.toString();
    }
    
    /**
     * 删除投融资项目的sql
     * 
     * @param uuid:投融资项目的唯一标识
     * 
     * @return：删除投融资项目信息的sql字符串
     */
    public static String deleteInvestSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_INVESTANDFINANCE WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    
    /**
     * 用户项目需求列表sql语句
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getFildOption(String tablename,String fieldname )
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.TABLENAME,T.FIELDNAME,T.OPTIONVALUE,T.OPTIONNAME FROM T_FIELDOPTION T ");
        sb.append(" WHERE T.TABLENAME='").append(tablename).append("'");
        sb.append(" AND T.FIELDNAME='").append(fieldname).append("'");
        sb.append(" ORDER BY T.TABLENAME,T.FIELDNAME,T.OPTIONVALUE,T.OPTIONNAME");
        return sb.toString();
    }
    
    /**
     * 根据技术需求uuid获取详细信息
     * 
     * @param uuid:技术需求的唯一标识
     * 
     * @return：sql字符串
     */
    public static String getTechneedInfoSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM T_TECHNEED WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 根据自荐项目uuid获取详细信息
     * 
     * @param uuid:自荐项目的唯一标识
     * 
     * @return：sql字符串
     */
    public static String getSelloneselfInfoSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM T_SELLONESELF WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 根据投融资项目uuid获取详细信息
     * 
     * @param uuid:投融资项目的唯一标识
     * 
     * @return：sql字符串
     */
    public static String getInvestInfoSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM T_INVESTANDFINANCE WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 选择审核通过的技术需求信息在首页上显示
     * **/
    public static String getTechneedHomePageShowSql(){
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_TECHNEED T WHERE IFCHECK ='1' ORDER BY SUBMITDATE DESC ");
        return sb.toString();
    }
    /**
     * 选择审核通过的自荐项目信息在首页上显示
     * **/
    public static String getSelloneselfHomePageShowSql(){
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_SELLONESELF T WHERE IFCHECK ='1' ORDER BY SUBMITDATE DESC ");
        return sb.toString();
    }
    /**
     * 选择审核通过的投融资项目信息在首页上显示
     * **/
    public static String getInvestHomePageShowSql(){
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_INVESTANDFINANCE T WHERE IFCHECK ='1' ORDER BY SUBMITDATE DESC ");
        return sb.toString();
    }
    /**
     * 选择推荐的的技术需求信息在首页上显示
     * **/
    public static String getTechneedRecommendShowSql(){
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_TECHNEED T WHERE IFCHECK ='1' AND HOMEPAGESHOW='1' ORDER BY SUBMITDATE DESC ");
        return sb.toString();
    }
    /**
     * 选择审核通过的自荐项目信息在首页上显示
     * **/
    public static String getSelloneselfRecommendShowSql(){
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_SELLONESELF T WHERE IFCHECK ='1' AND HOMEPAGESHOW='1' ORDER BY SUBMITDATE DESC ");
        return sb.toString();
    }
    /**
     * 选择审核通过的投融资项目信息在首页上显示
     * **/
    public static String getInvestRecommendShowSql(){
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_INVESTANDFINANCE T WHERE IFCHECK ='1' AND HOMEPAGESHOW='1' ORDER BY SUBMITDATE DESC ");
        return sb.toString();
    }
    
    
    public static String getTechneedByConditionSql(String infoclass,String searchday){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_TECHNEED T ");
    	sb.append(" WHERE IFCHECK ='1' ");
    	if(!"".equals(infoclass)){
    		sb.append(" AND NEWENERGYCLASS = '").append(infoclass).append("' ");
    	}
    	if(!"".equals(searchday)){
    		sb.append(" AND SUBMITDATE > '").append(searchday).append("' ");
    	}
    	sb.append(" ORDER BY SUBMITDATE DESC ");
    	
    	return sb.toString();
    }
    
    public static String getSelloneselfByConditionSql(String infoclass,String searchday){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_SELLONESELF T ");
    	sb.append(" WHERE IFCHECK ='1' ");
    	if(!"".equals(infoclass)){
    		sb.append(" AND NEWENERGYCLASS = '").append(infoclass).append("' ");
    	}
    	if(!"".equals(searchday)){
    		sb.append(" AND SUBMITDATE > '").append(searchday).append("' ");
    	}
    	sb.append(" ORDER BY SUBMITDATE DESC ");
    	
    	return sb.toString();
    }
    
    public static String getInvestByConditionSql(String infoclass,String searchday){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" SELECT T.ID,T.TITLE,T.LINKUNIT,T.LINKMAN,T.SUBMITDATE FROM T_INVESTANDFINANCE T ");
    	sb.append(" WHERE IFCHECK ='1' ");
    	if(!"".equals(infoclass)){
    		sb.append(" AND NEWENERGYCLASS = '").append(infoclass).append("' ");
    	}
    	if(!"".equals(searchday)){
    		sb.append(" AND SUBMITDATE > '").append(searchday).append("' ");
    	}
    	sb.append(" ORDER BY SUBMITDATE DESC ");
    	
    	return sb.toString();
    }
}
